﻿
--职级岗位异动发起查找员工信息
CREATE PROCEDURE [dbo].[proc_Employee_Search_AllUserName_Pove]
	(
		@Eid INT,
		@GID uniqueidentifier
	)
AS
begin		
	    Select
		Id,
		UserName,
		EName,
		CompanyId,
		DepartmentId,
		Isnull((Select PostName From Post Where Id=Employee.PostId),'...') As PostName,
		(Select CompanyName From Company Where Id=Employee.CompanyId) As CompanyName,
		Isnull((Select DName From Department Where Id=Employee.DepartmentId),'...') As DepartmentName,
		(Select Count(Customer.Id)  From Customer Where Customer.ProtectName=Employee.username and protectid=2) As UserPCount,
		(Select ProtectCount From Department Where Id=Employee.DepartmentId) As SystemPCount,
		ZhiWu,
		GradeId,
		(select Title from Grade where Id = GradeId) as GradeName,
		IsNull(PostCategory,0) as PostCategory,
		isnull((select Title from Post_Category where Post_Category.Id = PostCategory),'...') as PostCategoryName,
		PostId,
		Pluralist,
		(isnull((select Wage from Grade where id=Employee.GradeId),0)) as BasicWage,
		(isnull((select p.PostWage from Post p where p.id=Employee.PostId),0)) as PostWage,		
		(datediff(mm,ISNULL(Employee.RuzhiDate,GetDate()),GetDate())/12*100) as WorkWage,
		(isnull((select JxWage from Post where id=Employee.PostId),0)) as JxWage,
		(isnull((select ManQin from Post where id=Employee.PostId),0)) as ManQin,
		ISNULL((Select Sum(EA.Amount) From Employee_Allowance EA Where EA.PostId=Employee.PostId and CategoryId=0 and Employee.Pid=EA.Pid and Employee.Cid=EA.Cid),0) as Allowance,
		IsNull((Select Sum(FA.Amount) From Employee_FixedAllowance FA Where FA.UserName = Employee.UserName),0) as FixedAllowance,ISNULL(XZJB,'') AS XZJB,
		IsCore

		From Employee Where 		
	     id=@Eid		and GID=@GID
		
end
	RETURN